sql <-"select provider, analyte as parameter, extract(year from sample_date) as year, count(*) as n_measurementsfrom app.bottlewhere 1=1 and analyte = 'Chlorophyll'group by extract(year from sample_date), provider, analyteorder by year, provider"data <-get_data(sql)names(data) <-c(if('Global'!="Global") "Area", "Provider", "Parameter", "Year", "Measurement Count")if(nrow(data) ==0) {cat("No data available")} else {make_table(data, id ="Global_Bottle_Chlorophyll", name ="summary")}
Geographical Extent
Code
sql <-"select location_id, loc_desc as description, st_x(st_transform(loc_geom, 4326)) as lon, st_y(st_transform(loc_geom, 4326)) as lat, COUNT(*) AS record_count from app.bottlewhere 1=1 and loc_geom is not null and analyte = 'Chlorophyll'GROUP BY location_id, loc_desc, lon, lat"data <-get_data(sql) |>mutate(tooltip =glue("Location ID: {location_id}<br>Location Description: {description}<br>Sampe Count: {record_count}"))if(nrow(data) ==0) {cat("No data available")} else {# Plotly doesn't auto center map on data points with R center_lon <-mean(data$lon) center_lat <-mean(data$lat) zoom <-ifelse('Global'=="Global", 6, 8)plot_ly(data = data,lon =~lon,lat =~lat,type ='scattermapbox',mode ="markers",marker =list(size =9, line =list(width =1, color ="black")),hovertext =~tooltip ) |>layout(mapbox =list(style ="open-street-map",center =list(lon = center_lon, lat = center_lat),zoom = zoom ))}
Timeseries
Code
sql <-"select date_trunc('month', sample_date)::date as sample_date, count(*) as n_measurementsfrom app.bottlewhere 1=1 and analyte = 'Chlorophyll' and sample_date IS NOT NULLgroup by date_trunc('month', sample_date)::date"data <-get_data(sql)if(nrow(data) ==0) {cat("No data available")} else {plot_ly(data, x =~sample_date, y =~n_measurements, type ="bar") |>layout(title ="Chlorophyll Measurements by Month",xaxis =list(title ="Sample Date"),yaxis =list(title ="Number of Measurements", tickformat =",d") )}
Depth distribution
Code
sql <-"select upper_depth, depth_unitsfrom app.bottlewhere 1=1 and upper_depth is not null and depth_units is not null and analyte = 'Chlorophyll'"data <-get_data(sql)if(nrow(data) ==0) {cat("No data available")} else {# Define min and max depth for the x-axisx_min <-0# Minimum depthx_max <-350# Maximum depth# Create histogramplot_ly(data = data, x =~upper_depth, type ="histogram",xbins =list(start = x_min, # Set bin startend = x_max, # Set bin endsize =5# Bin width of 5 meters )) |>layout(title ="Chlorophyll Measurements by Depth",xaxis =list(title =glue("Depth ({toString(unique(data$depth_units))})"),range =c(x_min, x_max) # Ensure fixed depth range ),yaxis =list(title ="Number of Measurements", tickformat =",d") )}
sql <-"select analyte as parameter, extract(year from sample_date) as year, extract(month from sample_date) as month, count(*) as n_measurements, percentile_cont(0.5) within group (order by result) as median, string_agg(distinct units, ', ') as unitsfrom app.bottlewhere 1=1 and analyte = 'Chlorophyll'group by extract(year from sample_date), extract(month from sample_date), analyte "# query dbdata <-get_data(sql) |>filter(!is.na(year), !is.na(month))if(nrow(data) ==0){cat("No data available")}else{ data <- data |>mutate(abb_month =factor(month.abb[month], levels = month.abb),median =signif(median, digits =3),tooltip = glue::glue("<b>{abb_month} {year}</b>","<b>Count</b>: {format(n_measurements, big.mark = ',')}","<b>Median</b>: {median} {units}",.sep ="<br>",.na ="--" ) ) p <-ggplot(data = data, aes(x = abb_month, y = year, fill = median)) + ggiraph::geom_tile_interactive(aes(tooltip = tooltip), color ="black", width =1) +scale_fill_distiller(palette ="Greens",direction =1,na.value ="gray90" ) +#coord_fixed() + # keep tile as squarescale_x_discrete(breaks = month.abb,drop =FALSE,expand =c(0,0), position ="top" ) +scale_y_reverse(expand =c(0,0)) +labs(y ="Year", fill =paste0("Median Chlorophyll\n(",toString(unique(data$units)),")")) +theme_bw() +theme(axis.title.x=element_blank(),panel.grid.major =element_blank(),panel.grid.minor =element_blank(),#legend.position = "bottom" )ggiraph::girafe(ggobj = p) }
The heatmap data contains samples from all depths and locations in a given area.
Code
if(nrow(data) ==0) {cat("No data available")} else{ data |>select(#Area = area_id,Year = year,Month = abb_month,Parameter = parameter,Units = units,'Measurement Count'= n_measurements,Median = median ) |>make_table(id ="Global_Bottle_Chlorophyll", name ="heatmap")}
Data By Provider
Code
sql <-"select provider, analyte as parameter, extract(year from sample_date) as year, count(*) as n_measurementsfrom app.bottlewhere 1=1 and analyte = 'ChlorophyllA'group by extract(year from sample_date), provider, analyteorder by year, provider"data <-get_data(sql)names(data) <-c(if('Global'!="Global") "Area", "Provider", "Parameter", "Year", "Measurement Count")if(nrow(data) ==0) {cat("No data available")} else {make_table(data, id ="Global_Bottle_ChlorophyllA", name ="summary")}
Geographical Extent
Code
sql <-"select location_id, loc_desc as description, st_x(st_transform(loc_geom, 4326)) as lon, st_y(st_transform(loc_geom, 4326)) as lat, COUNT(*) AS record_count from app.bottlewhere 1=1 and loc_geom is not null and analyte = 'ChlorophyllA'GROUP BY location_id, loc_desc, lon, lat"data <-get_data(sql) |>mutate(tooltip =glue("Location ID: {location_id}<br>Location Description: {description}<br>Sampe Count: {record_count}"))if(nrow(data) ==0) {cat("No data available")} else {# Plotly doesn't auto center map on data points with R center_lon <-mean(data$lon) center_lat <-mean(data$lat) zoom <-ifelse('Global'=="Global", 6, 8)plot_ly(data = data,lon =~lon,lat =~lat,type ='scattermapbox',mode ="markers",marker =list(size =9, line =list(width =1, color ="black")),hovertext =~tooltip ) |>layout(mapbox =list(style ="open-street-map",center =list(lon = center_lon, lat = center_lat),zoom = zoom ))}
Timeseries
Code
sql <-"select date_trunc('month', sample_date)::date as sample_date, count(*) as n_measurementsfrom app.bottlewhere 1=1 and analyte = 'ChlorophyllA' and sample_date IS NOT NULLgroup by date_trunc('month', sample_date)::date"data <-get_data(sql)if(nrow(data) ==0) {cat("No data available")} else {plot_ly(data, x =~sample_date, y =~n_measurements, type ="bar") |>layout(title ="ChlorophyllA Measurements by Month",xaxis =list(title ="Sample Date"),yaxis =list(title ="Number of Measurements", tickformat =",d") )}
Depth distribution
Code
sql <-"select upper_depth, depth_unitsfrom app.bottlewhere 1=1 and upper_depth is not null and depth_units is not null and analyte = 'ChlorophyllA'"data <-get_data(sql)if(nrow(data) ==0) {cat("No data available")} else {# Define min and max depth for the x-axisx_min <-0# Minimum depthx_max <-350# Maximum depth# Create histogramplot_ly(data = data, x =~upper_depth, type ="histogram",xbins =list(start = x_min, # Set bin startend = x_max, # Set bin endsize =5# Bin width of 5 meters )) |>layout(title ="ChlorophyllA Measurements by Depth",xaxis =list(title =glue("Depth ({toString(unique(data$depth_units))})"),range =c(x_min, x_max) # Ensure fixed depth range ),yaxis =list(title ="Number of Measurements", tickformat =",d") )}
sql <-"select analyte as parameter, extract(year from sample_date) as year, extract(month from sample_date) as month, count(*) as n_measurements, percentile_cont(0.5) within group (order by result) as median, string_agg(distinct units, ', ') as unitsfrom app.bottlewhere 1=1 and analyte = 'ChlorophyllA'group by extract(year from sample_date), extract(month from sample_date), analyte "# query dbdata <-get_data(sql) |>filter(!is.na(year), !is.na(month))if(nrow(data) ==0){cat("No data available")}else{ data <- data |>mutate(abb_month =factor(month.abb[month], levels = month.abb),median =signif(median, digits =3),tooltip = glue::glue("<b>{abb_month} {year}</b>","<b>Count</b>: {format(n_measurements, big.mark = ',')}","<b>Median</b>: {median} {units}",.sep ="<br>",.na ="--" ) ) p <-ggplot(data = data, aes(x = abb_month, y = year, fill = median)) + ggiraph::geom_tile_interactive(aes(tooltip = tooltip), color ="black", width =1) +scale_fill_distiller(palette ="Greens",direction =1,na.value ="gray90" ) +#coord_fixed() + # keep tile as squarescale_x_discrete(breaks = month.abb,drop =FALSE,expand =c(0,0), position ="top" ) +scale_y_reverse(expand =c(0,0)) +labs(y ="Year", fill =paste0("Median ChlorophyllA\n(",toString(unique(data$units)),")")) +theme_bw() +theme(axis.title.x=element_blank(),panel.grid.major =element_blank(),panel.grid.minor =element_blank(),#legend.position = "bottom" )ggiraph::girafe(ggobj = p) }
The heatmap data contains samples from all depths and locations in a given area.
Code
if(nrow(data) ==0) {cat("No data available")} else{ data |>select(#Area = area_id,Year = year,Month = abb_month,Parameter = parameter,Units = units,'Measurement Count'= n_measurements,Median = median ) |>make_table(id ="Global_Bottle_ChlorophyllA", name ="heatmap")}
Data By Provider
Code
sql <-"select provider, parameter as parameter, extract(year from sample_date) as year, count(*) as n_measurementsfrom app.ctdwhere 1=1 and parameter = 'Chlorophyll'group by extract(year from sample_date), provider, parameterorder by year, provider"data <-get_data(sql)names(data) <-c(if('Global'!="Global") "Area", "Provider", "Parameter", "Year", "Measurement Count")if(nrow(data) ==0) {cat("No data available")} else {make_table(data, id ="Global_CTD_Chlorophyll", name ="summary")}
Geographical Extent
Code
sql <-"select location_id, loc_desc as description, st_x(st_transform(loc_geom, 4326)) as lon, st_y(st_transform(loc_geom, 4326)) as lat, COUNT(*) AS record_count from app.ctdwhere 1=1 and loc_geom is not null and parameter = 'Chlorophyll'GROUP BY location_id, loc_desc, lon, lat"data <-get_data(sql) |>mutate(tooltip =glue("Location ID: {location_id}<br>Location Description: {description}<br>Sampe Count: {record_count}"))if(nrow(data) ==0) {cat("No data available")} else {# Plotly doesn't auto center map on data points with R center_lon <-mean(data$lon) center_lat <-mean(data$lat) zoom <-ifelse('Global'=="Global", 6, 8)plot_ly(data = data,lon =~lon,lat =~lat,type ='scattermapbox',mode ="markers",marker =list(size =9, line =list(width =1, color ="black")),hovertext =~tooltip ) |>layout(mapbox =list(style ="open-street-map",center =list(lon = center_lon, lat = center_lat),zoom = zoom ))}
Timeseries
Code
sql <-"select date_trunc('month', sample_date)::date as sample_date, count(*) as n_measurementsfrom app.ctdwhere 1=1 and parameter = 'Chlorophyll' and sample_date IS NOT NULLgroup by date_trunc('month', sample_date)::date"data <-get_data(sql)if(nrow(data) ==0) {cat("No data available")} else {plot_ly(data, x =~sample_date, y =~n_measurements, type ="bar") |>layout(title ="Chlorophyll Measurements by Month",xaxis =list(title ="Sample Date"),yaxis =list(title ="Number of Measurements", tickformat =",d") )}
Depth distribution
Code
sql <-"select upper_depth, depth_unitsfrom app.ctdwhere 1=1 and upper_depth is not null and depth_units is not null and parameter = 'Chlorophyll'"data <-get_data(sql)if(nrow(data) ==0) {cat("No data available")} else {# Define min and max depth for the x-axisx_min <-0# Minimum depthx_max <-350# Maximum depth# Create histogramplot_ly(data = data, x =~upper_depth, type ="histogram",xbins =list(start = x_min, # Set bin startend = x_max, # Set bin endsize =5# Bin width of 5 meters )) |>layout(title ="Chlorophyll Measurements by Depth",xaxis =list(title =glue("Depth ({toString(unique(data$depth_units))})"),range =c(x_min, x_max) # Ensure fixed depth range ),yaxis =list(title ="Number of Measurements", tickformat =",d") )}
sql <-"select parameter as parameter, extract(year from sample_date) as year, extract(month from sample_date) as month, count(*) as n_measurements, percentile_cont(0.5) within group (order by result) as median, string_agg(distinct units, ', ') as unitsfrom app.ctdwhere 1=1 and parameter = 'Chlorophyll'group by extract(year from sample_date), extract(month from sample_date), parameter "# query dbdata <-get_data(sql) |>filter(!is.na(year), !is.na(month))if(nrow(data) ==0){cat("No data available")}else{ data <- data |>mutate(abb_month =factor(month.abb[month], levels = month.abb),median =signif(median, digits =3),tooltip = glue::glue("<b>{abb_month} {year}</b>","<b>Count</b>: {format(n_measurements, big.mark = ',')}","<b>Median</b>: {median} {units}",.sep ="<br>",.na ="--" ) ) p <-ggplot(data = data, aes(x = abb_month, y = year, fill = median)) + ggiraph::geom_tile_interactive(aes(tooltip = tooltip), color ="black", width =1) +scale_fill_distiller(palette ="Greens",direction =1,na.value ="gray90" ) +#coord_fixed() + # keep tile as squarescale_x_discrete(breaks = month.abb,drop =FALSE,expand =c(0,0), position ="top" ) +scale_y_reverse(expand =c(0,0)) +labs(y ="Year", fill =paste0("Median Chlorophyll\n(",toString(unique(data$units)),")")) +theme_bw() +theme(axis.title.x=element_blank(),panel.grid.major =element_blank(),panel.grid.minor =element_blank(),#legend.position = "bottom" )ggiraph::girafe(ggobj = p) }
The heatmap data contains samples from all depths and locations in a given area.
Code
if(nrow(data) ==0) {cat("No data available")} else{ data |>select(#Area = area_id,Year = year,Month = abb_month,Parameter = parameter,Units = units,'Measurement Count'= n_measurements,Median = median ) |>make_table(id ="Global_CTD_Chlorophyll", name ="heatmap")}
Data By Provider
Code
sql <-"select provider, parameter as parameter, extract(year from sample_date) as year, count(*) as n_measurementsfrom app.mooringwhere 1=1 and parameter = 'Chlorophyll'group by extract(year from sample_date), provider, parameterorder by year, provider"data <-get_data(sql)names(data) <-c(if('Global'!="Global") "Area", "Provider", "Parameter", "Year", "Measurement Count")if(nrow(data) ==0) {cat("No data available")} else {make_table(data, id ="Global_Mooring_Chlorophyll", name ="summary")}
Geographical Extent
Code
sql <-"select location_id, loc_desc as description, st_x(st_transform(loc_geom, 4326)) as lon, st_y(st_transform(loc_geom, 4326)) as lat, COUNT(*) AS record_count from app.mooringwhere 1=1 and loc_geom is not null and parameter = 'Chlorophyll'GROUP BY location_id, loc_desc, lon, lat"data <-get_data(sql) |>mutate(tooltip =glue("Location ID: {location_id}<br>Location Description: {description}<br>Sampe Count: {record_count}"))if(nrow(data) ==0) {cat("No data available")} else {# Plotly doesn't auto center map on data points with R center_lon <-mean(data$lon) center_lat <-mean(data$lat) zoom <-ifelse('Global'=="Global", 6, 8)plot_ly(data = data,lon =~lon,lat =~lat,type ='scattermapbox',mode ="markers",marker =list(size =9, line =list(width =1, color ="black")),hovertext =~tooltip ) |>layout(mapbox =list(style ="open-street-map",center =list(lon = center_lon, lat = center_lat),zoom = zoom ))}
Timeseries
Code
sql <-"select date_trunc('month', sample_date)::date as sample_date, count(*) as n_measurementsfrom app.mooringwhere 1=1 and parameter = 'Chlorophyll' and sample_date IS NOT NULLgroup by date_trunc('month', sample_date)::date"data <-get_data(sql)if(nrow(data) ==0) {cat("No data available")} else {plot_ly(data, x =~sample_date, y =~n_measurements, type ="bar") |>layout(title ="Chlorophyll Measurements by Month",xaxis =list(title ="Sample Date"),yaxis =list(title ="Number of Measurements", tickformat =",d") )}
Depth distribution
Code
sql <-"select upper_depth, depth_unitsfrom app.mooringwhere 1=1 and upper_depth is not null and depth_units is not null and parameter = 'Chlorophyll'"data <-get_data(sql)if(nrow(data) ==0) {cat("No data available")} else {# Define min and max depth for the x-axisx_min <-0# Minimum depthx_max <-350# Maximum depth# Create histogramplot_ly(data = data, x =~upper_depth, type ="histogram",xbins =list(start = x_min, # Set bin startend = x_max, # Set bin endsize =5# Bin width of 5 meters )) |>layout(title ="Chlorophyll Measurements by Depth",xaxis =list(title =glue("Depth ({toString(unique(data$depth_units))})"),range =c(x_min, x_max) # Ensure fixed depth range ),yaxis =list(title ="Number of Measurements", tickformat =",d") )}